-- Drop existing database if exists
DROP DATABASE IF EXISTS trading_journal;

-- Create new database
CREATE DATABASE trading_journal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE trading_journal;

-- Users table (for login)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','user') DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default admin user (password: admin)
INSERT INTO users (username, password, role) VALUES 
('admin', MD5('admin'), 'admin');

-- Accounts table
CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Symbols table
CREATE TABLE symbols (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Strategies table
CREATE TABLE strategies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Strategy Rules table
CREATE TABLE strategy_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    strategy_id INT NOT NULL,
    rule_text VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (strategy_id) REFERENCES strategies(id) ON DELETE CASCADE
);

-- Journal table with image columns added
CREATE TABLE journal (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL,
    symbol_id INT NOT NULL,
    strategy_id INT NOT NULL,
    timeframe ENUM('15M','1H','4H','1D','1Week') NOT NULL,
    trade_type ENUM('Buy','Sell') NOT NULL,
    quantity DECIMAL(12,4) NOT NULL,
    entry_price DECIMAL(12,4) NOT NULL,
    exit_price DECIMAL(12,4) NOT NULL,
    tp DECIMAL(12,4) NOT NULL,
    sl DECIMAL(12,4) NOT NULL,
    rr_ratio DECIMAL(5,2) NULL,
    profit_loss DECIMAL(12,2) NULL,
    percent_change DECIMAL(6,2) NULL,
    start_date DATE NOT NULL,
    close_date DATE NULL,
    follow_setup ENUM('Yes','No') NOT NULL DEFAULT 'Yes',
    emotions VARCHAR(255) NULL,
    comments TEXT NULL,
    img_before1 VARCHAR(255) NULL,
    img_before2 VARCHAR(255) NULL,
    img_after1 VARCHAR(255) NULL,
    img_after2 VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (symbol_id) REFERENCES symbols(id) ON DELETE CASCADE,
    FOREIGN KEY (strategy_id) REFERENCES strategies(id) ON DELETE CASCADE
);

-- Journal Rules table
CREATE TABLE journal_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    rule_id INT NOT NULL,
    FOREIGN KEY (journal_id) REFERENCES journal(id) ON DELETE CASCADE,
    FOREIGN KEY (rule_id) REFERENCES strategy_rules(id) ON DELETE CASCADE
);

-- Trading Watcher table
CREATE TABLE trading_watcher (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pair VARCHAR(50) NOT NULL,
    type ENUM('Buy','Sell') NOT NULL,
    target_price DECIMAL(12,4) NOT NULL,
    plan TEXT NULL,
    alert_date DATETIME NULL,
    status ENUM('Pending','Triggered','Closed') DEFAULT 'Pending',
    trade_taken ENUM('Yes','No') DEFAULT 'No',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    triggered_at DATETIME NULL,
    decision_at DATETIME NULL
);

-- Optional: Insert some sample data for testing
INSERT INTO accounts (name, balance) VALUES 
('Main Account', 1000),
('Trading Account', 500);

INSERT INTO symbols (name) VALUES ('BTC'), ('ETH'), ('XAU'), ('CAD'), ('JPY');

INSERT INTO strategies (name) VALUES ('Scalping'), ('Swing'), ('Day Trading');

-- Sample strategy rules
INSERT INTO strategy_rules (strategy_id, rule_text) VALUES
(1, 'Rule 1 for Scalping'),
(1, 'Rule 2 for Scalping'),
(2, 'Rule 1 for Swing'),
(2, 'Rule 2 for Swing'),
(3, 'Rule 1 for Day Trading'),
(3, 'Rule 2 for Day Trading');